package com.power.sifa.correction.dao.impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson.JSON;
import com.plf.core.common.dao.OffersetTransformers;
import com.power.sifa.approve.model.enumeration.Tp_gender;
import com.power.sifa.correction.dao.Cre_warningDaoCustom;
import com.power.sifa.correction.model.Cre_warning;
import com.power.sifa.correction.model.enumeration.St_workEnum;
import com.power.sifa.correction.model.enumeration.Tp_crc_userEnum;
import com.power.sifa.correction.model.pageInfo.Cre_record_gaPageInfo;
import com.power.sifa.correction.model.pageInfo.Cre_warningPageInfo;
import com.power.sifa.correction.model.pojo.vo.PhotoVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;

/**
 * 预警信息 持久层实现(hibernate)
 */

@Slf4j
@Repository
public class Cre_warningDaoImpl implements Cre_warningDaoCustom {

    @Value("${power.file.http-schema:''}")
    private String httpSchema;
    @Value("${power.file.domain:''}")
    private String domain;

    private final EntityManager entityManager;

    public Cre_warningDaoImpl(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Override
    public List queryList(Cre_warningPageInfo pageInfo) {
        List<Cre_warning> result = new ArrayList<>();

        StringBuffer sql = new StringBuffer("select a.*, b.crctype, b.crime, c.id as workid, c.userid, c.username, c.deptid, c.deptname, c.status, c.worktype from cre_warning a  join crc_person b on a.perid = b.id  join cre_work c on a.id = c.warnid where 1 = 1 ");
        List<Object> paramsList = new ArrayList<>();
        int index = 1;


        // 处置类型
        if (null != pageInfo.getF_issup()) {
            sql.append(" and a.issup = ?" + index++);
            paramsList.add(pageInfo.getF_issup());
        }
        // 预警类型
        if (!CollectionUtils.isEmpty(pageInfo.getF_type())) {
            sql.append(" and a.type in ?" + index++);
            paramsList.add(pageInfo.getF_type());
        }
        // 处置类型
        if (!CollectionUtils.isEmpty(pageInfo.getF_worktype())) {
            sql.append(" and c.worktype in ?" + index++);
            paramsList.add(pageInfo.getF_worktype());
        }
        // 状态
        if (!CollectionUtils.isEmpty(pageInfo.getF_status())) {
            sql.append(" and c.status in ?" + index++);
            paramsList.add(pageInfo.getF_status());
        }
        // 姓名查询
        if (StringUtils.isNotEmpty(pageInfo.getF_name())) {
            sql.append(" and (a.pername like ?" + index++);
            paramsList.add("%" + pageInfo.getF_name() + "%");
            sql.append(" or a.addr like ?" + index++ + " ) ");
            paramsList.add("%" + pageInfo.getF_name() + "%");
        }
        // 身份证号查询
        if (StringUtils.isNotEmpty(pageInfo.getF_cardno())) {
            sql.append(" and b.cardno like ?" + index++);
            paramsList.add("%" + pageInfo.getF_cardno() + "%");
        }

        // 罪名查询
        if (StringUtils.isNotEmpty(pageInfo.getF_crime())) {
            sql.append(" and b.crime like ?" + index++);
            paramsList.add("%" + pageInfo.getF_crime() + "%");
        }
        // 预警时间查询
        if (ObjectUtil.isNotEmpty(pageInfo.getF_rectime())) {
            sql.append(" and a.rectime >= ?" + index++);
            paramsList.add(pageInfo.getF_rectime());
        }
        if (ObjectUtil.isNotEmpty(pageInfo.getF_rectime_01())) {
            sql.append(" and a.rectime <= ?" + index++);
            paramsList.add(pageInfo.getF_rectime_01());
        }
        // 处置单位查询
        if (!CollectionUtils.isEmpty(pageInfo.getF_deptid())) {
            sql.append(" and c.deptid in ?" + index++);
            paramsList.add(pageInfo.getF_deptid());
        }

        // 排序
        sql.append(" order by a.rectime desc");
        Query query = entityManager.createNativeQuery(sql.toString());
        query.unwrap(NativeQueryImpl.class).setResultTransformer(OffersetTransformers.ALIAS_TO_ENTITY_MAP);
        log.info(sql.toString());

        // 添加条件
        for (int i = 0; i <= paramsList.size() - 1; i++) {
            query.setParameter(i + 1, paramsList.get(i));
        }
        // 查询总数
        pageInfo.setAllcount(query.getResultList().size());
        // 分页
        if (pageInfo.getAllcount() > 0) {
            int pageStart = (pageInfo.getPageNumber() - 1) * pageInfo.getPageSize();
            List<Map> list = query.setFirstResult(pageStart).setMaxResults(pageInfo.getPageSize()).getResultList();
            if (CollectionUtil.isNotEmpty(list)) {
                result = JSON.parseArray(JSON.toJSONString(list), Cre_warning.class);
            }
        }
        return result;
    }

    @Override
    public Page<Cre_warning> queryPage(Cre_warningPageInfo pageInfo) {
        List<Cre_warning> result = null;

        StringBuffer sql = new StringBuffer("select w.*, k.id as workid from cre_warning w join cre_work k on w.id = k.warnid where 1 = 1");
        List<Object> paramsList = new ArrayList<>();
        int index = 1;

        if (ObjectUtil.isNotEmpty(pageInfo.getF_type())) {
            sql.append(" and w.type in ?" + index++);
            paramsList.add(pageInfo.getF_type());
        }

        if (ObjectUtil.isNotEmpty(pageInfo.getF_status())) {
            sql.append(" and k.status in ?" + index++);
            paramsList.add(pageInfo.getF_status());
        }

        if (ObjectUtil.isNotEmpty(pageInfo.getF_rectime())) {
            sql.append(" and w.rectime >= ?" + index++);
            paramsList.add(pageInfo.getF_rectime());
        }

        if (ObjectUtil.isNotEmpty(pageInfo.getF_rectime_01())) {
            sql.append(" and w.rectime <= ?" + index++);
            paramsList.add(pageInfo.getF_rectime_01());
        }

        // 处置单位查询
        if (!CollectionUtils.isEmpty(pageInfo.getF_deptid())) {
            sql.append(" and k.deptid in ?" + index++);
            paramsList.add(pageInfo.getF_deptid());
        }

        sql.append(" order by w.rectime desc");
        Query query = entityManager.createNativeQuery(sql.toString());
        query.unwrap(NativeQueryImpl.class).setResultTransformer(OffersetTransformers.ALIAS_TO_ENTITY_MAP);
        for (int i = 0; i <= paramsList.size() - 1; i++) {
            query.setParameter(i + 1, paramsList.get(i));
        }

        pageInfo.setAllcount(query.getResultList().size());
        if (pageInfo.getAllcount() < 0) {
            return null;
        }
        int pageStart = (pageInfo.getPageNumber() - 1) * pageInfo.getPageSize();
        List<Map<String, Object>> list = query.setFirstResult(pageStart).setMaxResults(pageInfo.getPageSize()).getResultList();
        if (CollectionUtil.isEmpty(list)) {
            return null;
        }
        result = JSON.parseArray(JSON.toJSONString(list), Cre_warning.class);
        Pageable pageable = PageRequest.of(pageInfo.getPageNumber() - 1, pageInfo.getPageSize());
        Page<Cre_warning> creWarnings = new PageImpl<>(result, pageable, pageInfo.getAllcount());
        return creWarnings;
    }

    @Override
    public List<Cre_warning> findByTimeAndDeptid(Cre_warningPageInfo pageInfo) {
        List<Cre_warning> result = null;

        StringBuffer sql = new StringBuffer("select w.* from cre_warning w join crc_person p on w.perid = p.id where 1 = 1 ");
        List<Object> paramsList = new ArrayList<>();
        int index = 1;

        if (ObjectUtil.isNotEmpty(pageInfo.getF_deptid())) {
            sql.append(" and p.deptid in ?" + index++);
            paramsList.add(pageInfo.getF_deptid());
        }

        if (ObjectUtil.isNotEmpty(pageInfo.getF_rectime())) {
            sql.append(" and w.rectime >= ?" + index++);
            paramsList.add(pageInfo.getF_rectime());
        }

        if (ObjectUtil.isNotEmpty(pageInfo.getF_rectime_01())) {
            sql.append(" and w.rectime <= ?" + index++);
            paramsList.add(pageInfo.getF_rectime_01());
        }

        sql.append(" order by w.rectime desc");
        Query query = entityManager.createNativeQuery(sql.toString());
        query.unwrap(NativeQueryImpl.class).setResultTransformer(OffersetTransformers.ALIAS_TO_ENTITY_MAP);
        for (int i = 0; i <= paramsList.size() - 1; i++) {
            query.setParameter(i + 1, paramsList.get(i));
        }

        List<Map> list = query.getResultList();
        if (CollectionUtil.isNotEmpty(list)) {
            result = JSON.parseArray(JSON.toJSONString(list), Cre_warning.class);
        }

        return result;
    }

    @Override
    public List<PhotoVO> getPhotoList(Cre_record_gaPageInfo pageInfo) {

        return Collections.emptyList();
    }

    @Override
    public Page<Cre_warning> historicalWarning(Cre_warningPageInfo pageInfo, Pageable pageable) {
        List<Cre_warning> result = null;

        StringBuffer sql = new StringBuffer("select w.* " +
                "from Cre_warning w " +
                "left join cre_work k on w.id = k.warnid " +
                "where 1 = 1");
        List<Object> paramsList = new ArrayList<>();
        int index = 1;

        if (ObjectUtil.isNotEmpty(pageInfo.getF_status())) {
            sql.append(" and k.status in ?" + index++);
            paramsList.add(pageInfo.getF_status());
        }

        if (ObjectUtil.isNotEmpty(pageInfo.getF_perid())) {
            sql.append(" and perid = ?" + index++);
            paramsList.add(pageInfo.getF_perid());
        }

        sql.append(" order by w.rectime desc");
        Query query = entityManager.createNativeQuery(sql.toString());
        query.unwrap(NativeQueryImpl.class).setResultTransformer(OffersetTransformers.ALIAS_TO_ENTITY_MAP);
        for (int i = 0; i <= paramsList.size() - 1; i++) {
            query.setParameter(i + 1, paramsList.get(i));
        }

        pageInfo.setAllcount(query.getResultList().size());
        if (pageInfo.getAllcount() < 0) {
            return null;
        }
        int pageStart = (pageInfo.getPageNumber() - 1) * pageInfo.getPageSize();
        List<Map<String, Object>> list = query.setFirstResult(pageStart).setMaxResults(pageInfo.getPageSize()).getResultList();
        if (CollectionUtil.isEmpty(list)) {
            return null;
        }
        result = JSON.parseArray(JSON.toJSONString(list), Cre_warning.class);
        Page<Cre_warning> creWarnings = new PageImpl<>(result, pageable, pageInfo.getAllcount());
        return creWarnings;
    }

    @Override
    public Map<String, Object> findWarningRecordVO(String warnId) {
        StringBuffer sql = new StringBuffer("select p.name as name, p.gender as gender, p.id as number, " +
                "p.avatar as avatar, p.crctype as crctype, v.orgname as deptname, p.phone as phone, " +
                "p.cardno as cardno, w.rectime as rectime, w.addr as addr, k.status as verify, w.gaid as gaid, " +
                "(select cr.record from cre_work_record cr where cr.workid = workid and cr.ispis = 3201 order by cr.exectime desc limit 1) as disposal, " +
                "(select cr.record from cre_work_record cr where cr.workid = workid and cr.ispis = 3200 order by cr.exectime desc limit 1) as punishment " +
                "from crc_person p " +
                "left join v_rel_dept_correctuser v on p.deptid = v.adid " +
                "left join cre_warning w on p.id = w.perid " +
                "left join cre_work k on w.id = k.warnid " +
                "where 1 = 1");
        List<Object> paramsList = new ArrayList<>();
        int index = 1;

        if (ObjectUtil.isNotEmpty(warnId)) {
            sql.append(" and w.id = ?" + index++);
            paramsList.add(warnId);
        }
        Query query = entityManager.createNativeQuery(sql.toString());
//        query.unwrap(NativeQueryImpl.class).setResultTransformer(OffersetTransformers.ALIAS_TO_ENTITY_MAP);
        SQLQuery sqlQuery = query.unwrap(SQLQuery.class);
        sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        for (int i = 0; i <= paramsList.size() - 1; i++) {
            query.setParameter(i + 1, paramsList.get(i));
        }
        List<Map<String, Object>> list = sqlQuery.getResultList();
        if (CollectionUtil.isEmpty(list)) {
            return null;
        }
        Map<String, Object> map = list.get(0);
        if (CollectionUtil.isEmpty(map)) {
            return null;
        }
        if (map.get("rectime") != null) {
            String rectime = map.get("rectime").toString();
            map.put("rectime", rectime);
        }
        if (map.get("gender") != null) {
            map.put("gender", Tp_gender.GENDERMAP.get(map.get("gender")));
        }
        if (map.get("crctype") != null) {
            map.put("crctype", Tp_crc_userEnum.getTypeNameByValue((Integer) map.get("crctype")));
        }
        if (map.get("verify") != null) {
            map.put("verify", St_workEnum.getStatusNameByValue((Integer) map.get("verify")));
        }
        return map;
    }

    @Override
    public String getFileUrl(String fileId) {
        StringBuffer sql = new StringBuffer("select h.* from ha_attachment h where 1 = 1");

        List<Object> paramsList = new ArrayList<>();
        int index = 1;

        if (ObjectUtil.isNotEmpty(fileId)) {
            sql.append(" and h.id = ?" + index++);
            paramsList.add(fileId);
        }
        Query query = entityManager.createNativeQuery(sql.toString());
        SQLQuery sqlQuery = query.unwrap(SQLQuery.class);
        sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        for (int i = 0; i <= paramsList.size() - 1; i++) {
            query.setParameter(i + 1, paramsList.get(i));
        }
        List<Map<String, Object>> list = sqlQuery.getResultList();
        if (CollectionUtil.isEmpty(list)) {
            return null;
        }
        Map<String, Object> map = list.get(0);
        if (CollectionUtil.isEmpty(map)) {
            return null;
        }
        return httpSchema + domain + "/" + map.get("bucket") + "/" + map.get("path");
    }
}
